var express = require('express')
var router = express.Router()
var multipart = require('connect-multiparty');
const ExcelJS = require('exceljs');
const app = express();
// 允许文件下载
app.use(express.static('exports'));
// 密码强度验证规则
const PASSWORD_REGEX = /^(?=.*[a-z])(?=.*[A-Z])(?=.*\d)[a-zA-Z\d]{8,}/;
const bcrypt = require('bcrypt'); //  加密数字的模块
// middleware that is specific to this router
router.use(function timeLog(req, res, next) {
    console.log('Time: ', Date.now())
    //模块路由中检测是否携带了 token
    if (!req.auth) {
        return res.send({
            status: 403,
            message: "缺少token"
        });
    }
    next()
})
//  从db文件中导入的数据库模块
const db = require("../db/mysql");
const { pool } = require('../db/database')
const { json } = require('body-parser');

// 上传图片--单个图片--路由
// 定义路由处理文件上传请求
router.post('/upload', multipart(), function (req, res) {
    // 获取上传的文件信息
    console.log("aaaa", req.files)

    let one_f = req.files.picture;
    var originalFilename = one_f.originalFilename;
    var file_path = one_f.path;

    // 读取文件内容并保存到新位置
    if (file_path) {
        let date = new Date();
        let new_name = date.getTime() + path.extname(originalFilename);
        let new_file_name = 'public/uploads/' + new_name;

        // 把图片处理好的路径new_file_name存到名为user的数据库的picture的字段里
        db.query("", function (error, results) {
            if (error) {
                console.log(error)
            } else {
                res.json({
                    msg: "文件上传完成", code: 0, data: {
                        fileName: new_file_name,
                        ext: path.extname(new_file_name)
                    }
                })
            }

        })
        console.log(new_file_name)
        fs.rename(file_path, new_file_name, function (err) {
            if (err) {
                console.log(err)
                res.json({ status: -1, msg: err.message });
            } else {
                res.json({
                    msg: "文件上传完成", code: 0, data: {
                        fileName: new_file_name,
                        ext: path.extname(new_file_name)
                    }
                })
                // res.status(200).send('File uploaded successfully');
            }
        });
    }
});


// 统一的人员查询接口（支持列表和搜索）
router.get('/list', async (req, res) => {
    try {
        // 获取token信息
        console.log('Token信息:', req.auth);
        const uid = req.auth.uid;

        // 获取并验证查询参数
        let { 
            page = 1, 
            page_size = 10,
            uname, 
            account, 
            utype 
        } = req.query;

        // 转换和验证分页参数
        page = parseInt(page);
        page_size = parseInt(page_size);
        if (isNaN(page) || isNaN(page_size) || page < 1 || page_size < 1) {
            return res.status(400).json({
                code: 400,
                msg: "分页参数不合法",
                timestamp: Date.now()
            });
        }

        // 计算分页偏移量
        const start = (page - 1) * page_size;

        // 构建WHERE条件（使用参数化查询防止SQL注入）
        let whereConditions = ["a.status = 1"];
        const queryParams = [];

        if (uname) {
            whereConditions.push("a.uname LIKE ?");
            queryParams.push(`%${uname}%`);
        }

        if (account) {
            whereConditions.push("a.account LIKE ?");
            queryParams.push(`%${account}%`);
        }

        if (utype) {
            whereConditions.push("a.utype = ?");
            queryParams.push(utype);
        }

        const whereClause = whereConditions.length > 0 ? 
            `WHERE ${whereConditions.join(' AND ')}` : '';

        // 主查询SQL
        const selectSQL = `
            SELECT 
                a.id, a.uname, a.utype, a.account, 
                b.name as role_name, a.ctime, a.logintime
            FROM users as a 
            LEFT JOIN roles as b ON a.utype = b.id 
            ${whereClause}
            ORDER BY a.id DESC
            LIMIT ?, ?
        `;

        // 总数查询SQL
        const countSQL = `
            SELECT COUNT(*) as total 
            FROM users as a
            LEFT JOIN roles as b ON a.utype = b.id 
            ${whereClause}
        `;

        // 执行并行查询
        const [results, countData] = await Promise.all([
            new Promise((resolve, reject) => {
                db.query(selectSQL, [...queryParams, start, page_size], (error, results) => {
                    if (error) {
                        console.error('查询用户列表出错:', error);
                        reject(error);
                    } else {
                        resolve(results);
                    }
                });
            }),
            new Promise((resolve, reject) => {
                db.query(countSQL, queryParams, (err, data) => {
                    if (err) {
                        console.error('查询总数出错:', err);
                        reject(err);
                    } else {
                        resolve(data);
                    }
                });
            })
        ]);

        // 处理结果数据（确保不返回敏感信息）
        const list = results.map(user => ({
            id: user.id,
            name: user.uname,
            type: user.utype,
            mobile: user.account,
            role_name: user.role_name,
            create_time: user.ctime,
            last_login: user.logintime
        }));

        const totalNum = countData[0]?.total || 0;

        // 统一响应格式
        res.json({
            code: 0,
            msg: "查询成功",
            data: {
                list,
                pagination: {
                    current: page,
                    pageSize: page_size,
                    total: totalNum,
                    pages: Math.ceil(totalNum / page_size)
                }
            },
            timestamp: Date.now()
        });

    } catch (error) {
        console.error('查询人员列表异常:', error);
        res.status(500).json({
            code: 500,
            msg: "服务器内部错误",
            error: error.message,
            timestamp: Date.now()
        });
    }
});

// 导出Excel专用接口
router.get('/export_list', async (req, res) => {
    try {
        // 获取token信息
        console.log('Token信息:', req.auth);
        const uid = req.auth.uid;

        // 获取查询参数（不包含分页参数）
        let { uname, account, utype } = req.query;

        // 构建WHERE条件（使用参数化查询防止SQL注入）
        let whereConditions = ["a.status = 1"];
        const queryParams = [];

        if (uname) {
            whereConditions.push("a.uname LIKE ?");
            queryParams.push(`%${uname}%`);
        }

        if (account) {
            whereConditions.push("a.account LIKE ?");
            queryParams.push(`%${account}%`);
        }

        if (utype) {
            whereConditions.push("a.utype = ?");
            queryParams.push(utype);
        }

        const whereClause = whereConditions.length > 0 ? 
            `WHERE ${whereConditions.join(' AND ')}` : '';

        // 导出查询SQL（不包含LIMIT）
        const exportSQL = `
            SELECT 
                a.id, a.uname, a.utype, a.account, 
                b.name as role_name, a.ctime, a.logintime
            FROM users as a 
            LEFT JOIN roles as b ON a.utype = b.id 
            ${whereClause}
            ORDER BY a.id DESC
        `;

        // 执行查询
        const results = await new Promise((resolve, reject) => {
            db.query(exportSQL, queryParams, (error, results) => {
                if (error) {
                    console.error('导出查询出错:', error);
                    reject(error);
                } else {
                    resolve(results);
                }
            });
        });

        // 处理结果数据（确保不返回敏感信息）
        const list = results.map(user => ({
            id: user.id,
            name: user.uname,
            type: user.utype,
            mobile: user.account,
            role_name: user.role_name,
            create_time: user.ctime,
            last_login: user.logintime
        }));

        // 统一响应格式
        res.json({
            code: 0,
            msg: "查询成功",
            data: list,
            timestamp: Date.now()
        });

    } catch (error) {
        console.error('导出Excel异常:', error);
        res.status(500).json({
            code: 500,
            msg: "导出Excel失败",
            error: error.message,
            timestamp: Date.now()
        });
    }
});

// 人员添加
router.post('/add', (req, res) => {
    // 先获取用户的请求数据，存到变量“body”中
    var body = req.body
    // 用结构复制把用户请求数据里面的的“uname,part,mobile, pwd”分别拿出来，方便接下来向数据库中添加数据时使用
    let { uname, part, mobile, pwd } = body
    db.query('SELECT * FROM users where account = ?', [mobile], function (error, results) {
        if (results.length > 0) {
            // res.send('此手机号已经存在用户')
            res.send({ data: [], code: -1, msg: "此手机号已经存在用户" }) //标准返回信息
        } else {
            db.query(
                'INSERT INTO users (uname,utype,account,pwd) VALUES (?,?,?,?)',
                [uname, part, mobile, pwd],
                function (err, results) {
                    //  响应数据
                    if (err) {
                        res.json({ code: 1, msg: "操作失败", data: { err } })
                    } else {
                        res.json({ code: 0, msg: "添加完成", data: { uname: uname, DEPARTMENTt: part, mobile: mobile, password: pwd, } })
                    }
                    console.log(err + 'ok了!')
                });
        }
    })
    // res.json({ msg: "人员添加", code: 1, data: [] })
})
// 人员删除
router.get('/delete/:id', (req, res) => {
    console.log(req.params.id)
    db.query('DELETE FROM users WHERE id = ?', [parseInt(req.params.id)],
        function (error, result) {
            if (error) {
                res.json({ code: 1, msg: "删除失败", data: { err } })
            } else {
                res.json({ code: 0, msg: "删除成功" })
            }
        }
    )
})
// 多个人员的删除、
router.get('/deletes/:id', (req, res) => {
    console.log(req.params.id)
    let userids = req.params.id
    let useridps = JSON.parse(userids)
    let tiaojian = 'DELETE FROM users WHERE id IN (?)';
    db.query(tiaojian, [useridps], function (error, result) {
        if (error) {
            res.json({ code: 1, msg: "删除失败", data: { error: error } });
        } else {
            res.json({ code: 0, msg: "删除成功", data: { affectedRows: result.affectedRows } });
        }
    });
})
// 人员修改:获取信息--人员表
router.get('/edit/:id', (req, res) => {
    console.log(req.query, req.params)
    // 、！！！ get 取参数
    //  ?page=1&page_size=3,参数放在请求字符客户，问号开头的，req.query中 { page: '1', page_size: '3' }取
    //  /renyuan/edit/5 ,参数是动态路由 （/edit/:id），req.param中取参数 { id: '5' } 取
    if (!req.params.id) {
        res.send({ code: 1, msg: '缺少参数', data: [] });
    }
    let selectSQL = `
    SELECT a.id,a.uname,a.utype,a.account,a.pwd,a.picture,b.name as role_name 
    FROM users as a 
    left join roles as b 
    on a.utype = b.id
    WHERE a.id=?
    `;
    db.query(selectSQL, [parseInt(req.params.id)],
        function (error, results) {
            /*
            this.xiugaiobtainuid = response.data.id;
          this.edutruleForm.unamesave = response.data.uname;
          this.edutruleForm.umobilesave = response.data.account;
          this.edutruleForm.upartsave = response.data.role_name;
          this.edutruleForm.upwdsave = response.data.pwd;
            */
            var id;
            var uname;
            var account;
            var pwd;
            var role_name;
            var picture;
            var arr = [];
            for (let i = 0; i < results.length; i++) {
                id = results[i].id
                uname = results[i].uname
                account = results[i].account
                pwd = results[i].pwd
                role_name = results[i].role_name
                picture = results[i].picture
                arr.push({ id, uname, account, pwd, role_name, picture })
            }
            // 响应数据 ,扁平化数组 Array.flat(1)
            res.send({ code: 0, msg: '查询数据', data: arr });
        }
    );
})

// 人员修改:获取信息--个人信息修改
router.get('/edit_person/:account', (req, res) => {
    console.log(req.query, req.params)
    // 、！！！ get 取参数
    //  ?page=1&page_size=3,参数放在请求字符客户，问号开头的，req.query中 { page: '1', page_size: '3' }取
    //  /renyuan/edit/5 ,参数是动态路由 （/edit/:id），req.param中取参数 { id: '5' } 取
    if (!req.params.account) {
        res.send({ code: 1, msg: '缺少参数', data: [] });
    }
    let selectSQL = `
    SELECT a.id,a.uname,a.utype,a.account,a.pwd,a.picture,b.name as role_name 
    FROM users as a 
    left join roles as b 
    on a.utype = b.id
    WHERE a.account=?
    `;
    db.query(selectSQL, [parseInt(req.params.account)],
        function (error, results) {
            /*
            this.xiugaiobtainuid = response.data.id;
          this.edutruleForm.unamesave = response.data.uname;
          this.edutruleForm.umobilesave = response.data.account;
          this.edutruleForm.upartsave = response.data.role_name;
          this.edutruleForm.upwdsave = response.data.pwd;
            */
            var id;
            var uname;
            var account;
            var utype;
            var pwd;
            var role_name;
            var picture;
            var arr = [];
            for (let i = 0; i < results.length; i++) {
                id = results[i].id
                uname = results[i].uname
                account = results[i].account
                utype = results[i].utype
                pwd = results[i].pwd
                role_name = results[i].role_name
                picture = results[i].picture
                arr.push({ id, uname, account, pwd, role_name, picture, utype })
            }
            // 响应数据 ,扁平化数组 Array.flat(1)
            res.send({ code: 0, msg: '查询数据', data: arr });
        }
    );
})

// 人员修改:保存信息--个人信息修改
// 人员信息修改
router.post('/save_edit/:id', async (req, res) => {
    const { id } = req.params;
    let { uname, mobile, pwd, picture, part } = req.body;

    try {
        // 构建更新字段
        const updateFields = {
            uname,
            utype: part,
            account: mobile,
            picture
        };

        // 如果有密码则加密更新
        if (pwd) {
            if (!PASSWORD_REGEX.test(pwd)) {
                return res.status(400).json({ code: -1, msg: '密码必须包含大小写字母和数字，且长度至少8位' });
            }
            updateFields.pwd = await bcrypt.hash(pwd, 10);
        }

        // 执行更新
        const [result] = await pool.query(
            'UPDATE users SET ? WHERE id = ?',
            [updateFields, id]
        );

        if (result.affectedRows === 0) {
            return res.status(404).json({ code: -1, msg: '用户不存在' });
        }

        res.json({
            code: 0,
            msg: "修改成功",
            data: { id, uname, mobile, picture }
        });

    } catch (err) {
        console.error('修改信息错误:', err);
        res.status(500).json({ code: -1, msg: "服务器错误", error: err.message });
    }
});

// 人员详情
router.get('/details/:id', (req, res) => {
    db.query('SELECT * FROM users WHERE id = ?', [
        req.params.id], function (error, result) {
            // 响应数据
            res.json({ msg: "人员详情", code: 1, data: result })
        });
    // res.json({ msg: "人员详情", code: 1, data: [result] })
})
// 导出excelJs文件
router.get('/download', (req, res) => {
    // 创建工作簿和工作表
    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('My Sheet');
    let selectSQL = `
    SELECT a.id,a.uname,a.utype,a.account,b.name as role_name 
    FROM users as a 
    left join roles as b 
    on a.utype = b.id`;
    db.query(selectSQL, function (error, results) {
        if (error) console.log(error)
        var id;
        var uname;
        var part;
        var mobile;
        var pwd;
        var part_name;
        var arr = [];
        for (let i = 0; i < results.length; i++) {
            id = results[i].id
            uname = results[i].uname
            part = results[i].utype
            mobile = results[i].account
            pwd = results[i].pwd
            part_name = results[i].role_name

            arr.push({ id: id, uname: uname, part: part, mobile: mobile, pwd: pwd, part_name: part_name })
            console.log(part)
        }
        // 添加数据
        // worksheet.addRows(arr);
        res.json({ code: 0, msg: "人员列表", data: arr })
    })
    // 设置响应头并发送文件
    // res.setHeader('Content-Disposition', 'attachment; filename=myExcelFile.xlsx');
    // workbook.xlsx.write(res)
    //   .then(() => {
    //     res.end();
    //   });
});
module.exports = router;